package com.cardone.common.util;

import com.cardone.context.action.*;
import com.cardone.context.function.*;
import com.google.common.collect.*;
import lombok.*;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.*;
import org.apache.poi.openxml4j.exceptions.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.*;
import java.io.*;
import java.math.*;
import java.net.*;
import java.util.*;
import java.util.Map.*;

/**
 * excel工具类
 *
 * @author yaohaitao
 */
public class ExcelUtils {
    private ExcelUtils() {
    }

    /**
     * 读取文件名
     *
     * @param filename        文件名
     * @param excelConfigList excel配置集合
     * @throws InvalidFormatException
     * @throws IOException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static void readFilename(final String filename, final List<ExcelConfig> excelConfigList) throws InvalidFormatException, IOException, InstantiationException, IllegalAccessException {
        ExcelUtils.readFile(new File(filename), excelConfigList);
    }

    /**
     * 读取文件
     *
     * @param file            文件
     * @param excelConfigList excel配置集合
     * @throws InvalidFormatException
     * @throws IOException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static void readFile(final File file, final List<ExcelConfig> excelConfigList) throws InvalidFormatException, IOException, InstantiationException, IllegalAccessException {
        final Workbook workbook = WorkbookFactory.create(file);

        ExcelUtils.readWorkbookForExcelConfigs(workbook, excelConfigList);
    }

    /**
     * 读取工作薄
     *
     * @param workbook        工作薄
     * @param excelConfigList excel配置集合
     * @return
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static void readWorkbookForExcelConfigs(final Workbook workbook, final List<ExcelConfig> excelConfigList) throws InstantiationException, IllegalAccessException {
        for (final ExcelConfig excelConfig : excelConfigList) {
            ExcelUtils.readWorkbookForExcelConfig(workbook, excelConfig);
        }
    }

    /**
     * 读取工作薄
     *
     * @param workbook    工作薄
     * @param excelConfig excel配置
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static void readWorkbookForExcelConfig(final Workbook workbook, final ExcelConfig excelConfig) throws InstantiationException, IllegalAccessException {
        if (ArrayUtils.isNotEmpty(excelConfig.getSheetIndexs())) {
            for (final int sheetIndex : excelConfig.getSheetIndexs()) {
                final Sheet sheet = workbook.getSheetAt(sheetIndex);

                if (excelConfig.getAction() != null) {
                    excelConfig.getAction().run(sheet);
                }

                ExcelUtils.readSheet(sheet, excelConfig);
            }

            return;
        }

        if (ArrayUtils.isEmpty(excelConfig.getSheetNames())) {
            return;
        }

        for (final String sheetName : excelConfig.getSheetNames()) {
            final Sheet sheet = workbook.getSheet(sheetName);

            if (excelConfig.getAction() != null) {
                excelConfig.getAction().run(sheet);
            }

            ExcelUtils.readSheet(sheet, excelConfig);
        }
    }

    /**
     * 读取Sheet
     *
     * @param sheet       Sheet
     * @param excelConfig excel配置
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static void readSheet(final Sheet sheet, final ExcelConfig excelConfig) throws InstantiationException, IllegalAccessException {
        int rowIndex = 0;

        do {
            final int rownum = (rowIndex++) + excelConfig.getFirstRowIndex();

            final Row row = sheet.getRow(rownum);

            if (row == null) {
                return;
            }

            Map<String, Object> rowData = null;

            if (excelConfig.getReadRowFunction() == null) {
                rowData = ExcelUtils.readRow(row, excelConfig.getColumns());
            } else {
                rowData = excelConfig.getReadRowFunction().execution(row, rownum, excelConfig.getColumns());
            }

            if (excelConfig.getDataList() == null) {
                excelConfig.setDataList(Lists.<Map<String, Object>>newArrayList());
            }

            excelConfig.getDataList().add(rowData);

            if (excelConfig.getValidateData() == null) {
                continue;
            }

            if (BooleanUtils.isTrue(excelConfig.getValidateData().execution(rowData))) {
                if (excelConfig.getSucceedDataList() == null) {
                    excelConfig.setSucceedDataList(Lists.<Map<String, Object>>newArrayList());
                }

                excelConfig.getSucceedDataList().add(rowData);

                continue;
            }

            if (excelConfig.getErrorDataList() == null) {
                excelConfig.setErrorDataList(Lists.<Map<String, Object>>newArrayList());
            }

            excelConfig.getErrorDataList().add(rowData);
        } while (true);
    }

    /**
     * 读取行
     *
     * @param row     行
     * @param columns 列定义
     * @return
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static Map<String, Object> readRow(final Row row, final Map<Integer, String> columns) throws InstantiationException, IllegalAccessException {
        final Map<String, Object> map = Maps.newHashMap();

        for (final Entry<Integer, String> columnEntry : columns.entrySet()) {
            final Cell cell = row.getCell(columnEntry.getKey());

            if (cell == null) {
                map.put(columnEntry.getValue(), null);

                continue;
            }

            final int type = cell.getCellType();

            if (type == Cell.CELL_TYPE_BOOLEAN) {
                map.put(columnEntry.getValue(), cell.getBooleanCellValue());
            } else if (type == Cell.CELL_TYPE_NUMERIC) {
                map.put(columnEntry.getValue(), cell.getNumericCellValue());
            } else {
                map.put(columnEntry.getValue(), cell.getStringCellValue());
            }
        }

        return map;
    }

    /**
     * 读取文件流
     *
     * @param is              InputStream
     * @param excelConfigList excel配置集合
     * @return
     * @throws InvalidFormatException
     * @throws IOException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static void readInputStream(final InputStream is, final List<ExcelConfig> excelConfigList) throws InvalidFormatException, IOException, InstantiationException, IllegalAccessException {
        final Workbook workbook = WorkbookFactory.create(is);

        ExcelUtils.readWorkbookForExcelConfigs(workbook, excelConfigList);
    }

    /**
     * 写入文件名
     *
     * @param filename        文件名
     * @param excelConfigList excel配置集合
     * @throws InvalidFormatException
     * @throws IOException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static void writeFilename(final String filename, final List<ExcelConfig> excelConfigList) throws InvalidFormatException, IOException, InstantiationException, IllegalAccessException {
        ExcelUtils.writeFile(new File(filename), excelConfigList);
    }

    /**
     * 写入文件
     *
     * @param file            文件
     * @param excelConfigList excel配置集合
     * @throws InvalidFormatException
     * @throws IOException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static void writeFile(final File file, final List<ExcelConfig> excelConfigList) throws InvalidFormatException, IOException, InstantiationException, IllegalAccessException {
        final Workbook workbook = WorkbookFactory.create(file);

        ExcelUtils.writeWorkbookForExcelConfigs(workbook, excelConfigList);
    }

    /**
     * 写入工作薄
     *
     * @param workbook        工作薄
     * @param excelConfigList excel配置集合
     * @return
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static void writeWorkbookForExcelConfigs(final Workbook workbook, final List<ExcelConfig> excelConfigList) throws InstantiationException, IllegalAccessException {
        for (final ExcelConfig excelConfig : excelConfigList) {
            ExcelUtils.writeWorkbookForExcelConfig(workbook, excelConfig);
        }
    }

    /**
     * 写入工作薄
     *
     * @param workbook    工作薄
     * @param excelConfig excel配置
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static void writeWorkbookForExcelConfig(final Workbook workbook, final ExcelConfig excelConfig) throws InstantiationException, IllegalAccessException {
        if (ArrayUtils.isNotEmpty(excelConfig.getSheetIndexs())) {
            for (final int sheetIndex : excelConfig.getSheetIndexs()) {
                final Sheet sheet = workbook.getSheetAt(sheetIndex);

                if (excelConfig.getAction() != null) {
                    excelConfig.getAction().run(sheet);
                }

                ExcelUtils.writeSheet(sheet, excelConfig);
            }

            return;
        }

        if (ArrayUtils.isEmpty(excelConfig.getSheetNames())) {
            return;
        }

        for (final String sheetName : excelConfig.getSheetNames()) {
            final Sheet sheet = workbook.getSheet(sheetName);

            if (excelConfig.getAction() != null) {
                excelConfig.getAction().run(sheet);
            }

            ExcelUtils.writeSheet(sheet, excelConfig);
        }
    }

    /**
     * 写入Sheet
     *
     * @param sheet       Sheet
     * @param excelConfig excel配置
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static void writeSheet(final Sheet sheet, final ExcelConfig excelConfig) throws InstantiationException, IllegalAccessException {
        if (CollectionUtils.isEmpty(excelConfig.getDataList())) {
            return;
        }

        int rowIndex = 0;

        for (final Map<String, Object> rowData : excelConfig.getDataList()) {
            final int rownum = (rowIndex++) + excelConfig.getFirstRowIndex();

            final Row row = sheet.createRow(rownum);

            for (final Entry<Integer, String> columnEntry : excelConfig.getColumns().entrySet()) {
                final Object value = rowData.get(columnEntry.getValue());

                if (value == null) {
                    continue;
                }

                final Cell cell = row.createCell(columnEntry.getKey());

                if (value instanceof Double) {
                    cell.setCellValue((Double) value);
                } else if (value instanceof BigDecimal) {
                    cell.setCellValue(value.toString());
                } else if (value instanceof Date) {
                    cell.setCellValue((Date) value);
                } else if (value instanceof Integer) {
                    cell.setCellValue((Integer) value);
                } else if (value instanceof Boolean) {
                    cell.setCellValue((Boolean) value);
                } else if (value instanceof Calendar) {
                    cell.setCellValue((Calendar) value);
                } else {
                    cell.setCellValue(String.valueOf(value));
                }
            }
        }
    }

    /**
     * 写入流
     *
     * @param response
     * @param downFilename     下载文件名
     * @param templateFileName 模板文件名称
     * @param excelConfigList  excel配置集合
     * @throws Exception
     */
    public static void writeHttpServletResponse(final HttpServletResponse response, final String downFilename, final String templateFileName, final List<ExcelConfig> excelConfigList) throws Exception {
        response.setContentType("application/x-msdownload");
        response.setHeader("content-disposition", "attachment; filename=" + URLEncoder.encode(downFilename, "UTF-8"));

        final Workbook templateWorkbook = WorkbookFactory.create(new File(templateFileName));

        final Workbook workbook = new XSSFWorkbook();

        for (final ExcelConfig excelConfig : excelConfigList) {
            for (final int sheetIndex : excelConfig.getSheetIndexs()) {
                final Sheet templateheet = templateWorkbook.getSheetAt(sheetIndex);

                if (templateheet == null) {
                    continue;
                }

                final Sheet sheet = workbook.createSheet(templateheet.getSheetName());

                ExcelUtils.copySheet(templateheet, sheet);

                ExcelUtils.writeSheet(sheet, excelConfig);
            }
        }

        try (java.io.OutputStream out = response.getOutputStream()) {
            workbook.write(out);

            out.flush();

            response.flushBuffer();
        }
    }

    /**
     * 复制Sheet
     *
     * @param templateSheet 模板sheet
     * @param sheet
     */
    private static void copySheet(final Sheet templateSheet, final Sheet sheet) {
        for (int i = 0; i < templateSheet.getLastRowNum(); i++) {
            final Row templateRow = templateSheet.getRow(i);

            if (templateRow == null) {
                continue;
            }

            final Row newRow = sheet.createRow(i);

            for (int j = 0; j < templateRow.getLastCellNum(); j++) {
                final Cell templateCell = templateRow.getCell((short) j);

                if (templateCell == null) {
                    continue;
                }

                final Cell newCell = newRow.createCell((short) j);

                newCell.setCellStyle(sheet.getWorkbook().createCellStyle());

                ExcelUtils.copyCell(templateCell, newCell);
            }
        }
    }

    /**
     * 复制列
     *
     * @param templateCell 模板列
     * @param cell         列
     */
    private static void copyCell(final Cell templateCell, final Cell cell) {
        cell.getCellStyle().cloneStyleFrom(templateCell.getCellStyle());

        if (templateCell.getCellComment() != null) {
            cell.setCellComment(templateCell.getCellComment());
        }

        final int srcCellType = templateCell.getCellType();

        cell.setCellType(srcCellType);

        if (srcCellType == Cell.CELL_TYPE_NUMERIC) {
            if (DateUtil.isCellDateFormatted(templateCell)) {
                cell.setCellValue(templateCell.getDateCellValue());
            } else {
                cell.setCellValue(templateCell.getNumericCellValue());
            }
        } else if (srcCellType == Cell.CELL_TYPE_STRING) {
            cell.setCellValue(templateCell.getRichStringCellValue());
        } else if (srcCellType == Cell.CELL_TYPE_BOOLEAN) {
            cell.setCellValue(templateCell.getBooleanCellValue());
        } else if (srcCellType == Cell.CELL_TYPE_ERROR) {
            cell.setCellErrorValue(templateCell.getErrorCellValue());
        } else if (srcCellType == Cell.CELL_TYPE_FORMULA) {
            cell.setCellFormula(templateCell.getCellFormula());
        }
    }

    /**
     * 写入文件流
     *
     * @param is              InputStream
     * @param excelConfigList excel配置集合
     * @return
     * @throws InvalidFormatException
     * @throws IOException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static void writeInputStream(final InputStream is, final List<ExcelConfig> excelConfigList) throws InvalidFormatException, IOException, InstantiationException, IllegalAccessException {
        final Workbook workbook = WorkbookFactory.create(is);

        ExcelUtils.writeWorkbookForExcelConfigs(workbook, excelConfigList);
    }

    /**
     * 写入行
     *
     * @param row     行
     * @param columns 列定义
     * @return
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static Map<String, Object> writeRow(final Row row, final Map<Integer, String> columns) throws InstantiationException, IllegalAccessException {
        final Map<String, Object> map = Maps.newHashMap();

        for (final Entry<Integer, String> columnEntry : columns.entrySet()) {
            final Cell cell = row.getCell(columnEntry.getKey());

            if (cell == null) {
                map.put(columnEntry.getValue(), null);

                continue;
            }

            final int type = cell.getCellType();

            if (type == Cell.CELL_TYPE_BOOLEAN) {
                map.put(columnEntry.getValue(), cell.getBooleanCellValue());
            } else if (type == Cell.CELL_TYPE_NUMERIC) {
                map.put(columnEntry.getValue(), cell.getNumericCellValue());
            } else {
                map.put(columnEntry.getValue(), cell.getStringCellValue());
            }
        }

        return map;
    }

    /**
     * excel配置
     *
     * @author yaohaitao
     */
    @Getter
    @Setter
    public static class ExcelConfig {
        /**
         * sheet索引集合
         */
        int[] sheetIndexs = {0};
        /**
         * sheet名称集合
         */
        String[] sheetNames;
        /**
         * 动作
         */
        private Run1Action<Sheet> action;
        /**
         * 列定义
         */
        private Map<Integer, String> columns;
        /**
         * 数据集合
         */
        private List<Map<String, Object>> dataList;
        /**
         * 错误数据集合
         */
        private List<Map<String, Object>> errorDataList;
        /**
         * 开始行索引
         */
        private int firstRowIndex;
        /**
         * 读取行方法
         */
        private Execution3Function<Map<String, Object>, Row, Integer, Map<Integer, String>> readRowFunction;
        /**
         * 成功数据集合
         */
        private List<Map<String, Object>> succeedDataList;
        /**
         * 验证方法
         */
        private Execution1Function<Boolean, Map<String, Object>> validateData;
        /**
         * 写入动作
         */
        private Run3Action<Map<String, Object>, Row, Integer> writeRowAction;

        /**
         * 禁止实例化
         */
        private ExcelConfig() {
        }

        /**
         * 实例化
         *
         * @param columns 列定义
         * @return excel配置
         */
        public static ExcelConfig newExcelConfig(final Map<Integer, String> columns) {
            final ExcelConfig excelConfig = new ExcelConfig();

            excelConfig.setColumns(columns);

            return excelConfig;
        }
    }
}
